﻿using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Data.Sql;
using Microsoft.SqlServer.Server;

public partial class FileImport
{
    [Microsoft.SqlServer.Server.SqlProcedure]
    public static void ImportFromFile( SqlString tableName, SqlString fileName, SqlString templateFile)
    {
        SqlConnection conn = new SqlConnection(@"context connection=true");
        try
        {
            conn.Open();
        }
        catch (Exception e)
        {
            throw new Exception("Unable to open database\n" + e.Message);
        }

        DataSet ds = new DataSet();
        SqlDataAdapter da = new SqlDataAdapter();
        SqlCommand cmm = new SqlCommand();
        cmm.CommandText = "select * from " + tableName.ToString();
        cmm.Connection = conn;
        da.SelectCommand = cmm;
        SqlCommandBuilder cb = new SqlCommandBuilder(da);
        try
        {
            da.FillSchema(ds, SchemaType.Source);
        }
        catch (Exception err)
        {
            throw new Exception("Unable to load table schema", err);
        }

        DataImport di = new DataImport();
        try
        {
            di.LoadData(fileName.ToString(), ds.Tables[0], templateFile.ToString());
        }
        catch (Exception err)
        {
            throw new Exception("Unable to load the file into the datatable", err);
        }

        try
        {
            da.Update(ds);
        }
        catch (Exception err)
        {
            throw new Exception("Unable to write data to the table", err);
        }
    }
}

